package com.example.demo.web.dao;

import java.util.List;
import java.util.UUID;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.util.XinLongUtil;


@Repository
public class OrganizationDAO {
	private Logger log = LogManager.getLogger(OrganizationDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
    
    public int updateorg(OrganizationEntity orgEntity) throws EmptyResultDataAccessException{
		// update
		String sql = "UPDATE tbl_organization SET Createtime=?, Updatetime=?, Createchecker=?, Updatechecker=?, Createname=?"
					+ ",Updatename=?, Address=?, Orgcode=?, Leader=?, Name=?, Orgtype=?, Parentid=?, Status=?, Manager_id=?, Descriptor=? WHERE ID=?";
		return jdbcTemplate.update(sql, orgEntity.getCreatetime(),orgEntity.getUpdatetime(),orgEntity.getCreatechecker(),orgEntity.getUpdatechecker(),
				orgEntity.getCreatename(),orgEntity.getUpdatename(),orgEntity.getAddress(),orgEntity.getOrgcode(),orgEntity.getLeader(),orgEntity.getName(),
				orgEntity.getOrgtype(),orgEntity.getParentid(),orgEntity.getStatus(),orgEntity.getManager_id(),orgEntity.getDescriptor(),orgEntity.getId());    	
    };
    
    public int saveorg(OrganizationEntity orgEntity){
    	// insert
    	String uId = UUID.randomUUID().toString().replace("-", "");
		String sql = "INSERT INTO tbl_organization (id,Createtime,Updatetime, Createchecker,Updatechecker,Createname, Updatename, Address, Orgcode,Leader,Name,Orgtype,Parentid, Status, Manager_id, Descriptor)"
                + " VALUES (?, ?, ?, ?,?,?, ?, ?, ?,?,?, ?, ?, ?,?,?)";
    	return jdbcTemplate.update(sql, uId,orgEntity.getCreatetime(),orgEntity.getUpdatetime(),orgEntity.getCreatechecker(),orgEntity.getUpdatechecker(),
				orgEntity.getCreatename(),orgEntity.getUpdatename(),orgEntity.getAddress(),orgEntity.getOrgcode(),orgEntity.getLeader(),orgEntity.getName(),
				orgEntity.getOrgtype(),orgEntity.getParentid(),orgEntity.getStatus(),orgEntity.getManager_id(),orgEntity.getDescriptor()); 	
    };
    
    public List<OrganizationEntity> findSubOrg(String parentid) throws EmptyResultDataAccessException{
    	String sql = "SELECT org.*,porg.Name as parent_name FROM tbl_organization org, tbl_organization porg WHERE org.Parentid='" + parentid + "' and org.Parentid = porg.id order by org.Orgcode";        
    	return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public OrganizationEntity findByparentid(String pid) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_organization WHERE Parentid='" + pid + "'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    }
    
    public OrganizationEntity findByManagername(String mname) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_organization WHERE Managername='" + mname + "'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public OrganizationEntity findByOrgcode(String orgcode) throws EmptyResultDataAccessException{
    	String sql = "SELECT org.*,porg.Name as parent_name FROM tbl_organization org left join tbl_organization porg on org.Parentid = porg.id WHERE org.Orgcode='" + orgcode +"'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public OrganizationEntity findByName(String pname) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_organization WHERE Name='" + pname + "'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public String findNameByID(String id) throws EmptyResultDataAccessException{
    	String sql = "SELECT Name FROM tbl_organization WHERE id='" + id + "'";
        
    	return  jdbcTemplate.queryForObject(sql, String.class);
    };
    
    public String findCodeByID(String id) throws EmptyResultDataAccessException{
    	String sql = "SELECT Orgcode FROM tbl_organization WHERE id='" + id + "'";
        try{
        	return  jdbcTemplate.queryForObject(sql, String.class);
        }catch(Exception e){
        	return "";
        }
    	
    };
    
    public OrganizationEntity findExist(String name,String orgcode) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_organization WHERE Name='" + name + "' or Orgcode = '" + orgcode + "'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public OrganizationEntity findNameExist(String name,String orgcode) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_organization WHERE Name='" + name + "' and Orgcode != '" + orgcode + "'";
        
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    };
    
    public int delete(String orgcode) {
        String sql = "DELETE FROM tbl_organization WHERE Orgcode=?";
        try{
        	return jdbcTemplate.update(sql, orgcode);
        }catch(DataIntegrityViolationException de){
        	XinLongUtil.xinLongUtil.printlog(log,de.getMessage());
        	return 11;
        }
        catch(Exception ex){
        	XinLongUtil.xinLongUtil.printlog(log,ex.getMessage());
        	return 0;
        }
        
    }
    
    public List<OrganizationEntity> findSubOrgs(String id){
    	String sql = "SELECT DATA.* FROM(SELECT @ids as _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM tbl_organization " + 
    			"WHERE FIND_IN_SET(Parentid, @ids) " + 
    			") as cids, " + 
    			"@l := @l+1 as level " + 
    			"FROM tbl_organization, (SELECT @ids :='" + id + "', @l := 0 ) b " + 
    			"WHERE @ids IS NOT NULL " + 
    			") id, tbl_organization DATA " + 
    			"WHERE FIND_IN_SET(DATA.id, ID._ids)";        
    	return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    }
    
}
